@page "/database-connection/create"
@page "/database-connection/create/{ConId}"
@using AntDesign
@using AntDesign.Form
@using Microsoft.AspNetCore.Components.Forms
@using System.IO
@using System.Linq
@using System.Collections.Generic
@using MiniExcelLibs
@using Text2Sql.Net.Domain.Model
@using Text2Sql.Net.Repositories.Text2Sql
@using Text2Sql.Net.Repositories.Text2Sql.DatabaseConnection
@inject IDatabaseConnectionConfigRepository DatabaseConnectionConfigRepository
@inject MessageService MessageService
@inject NavigationManager NavigationManager

<PageContainer Title="添加数据库连接">

    <ChildContent>
        <Card>
            <Body>
                <Form @ref="@_form" Model="@_model" LabelColSpan="6" WrapperColSpan="18" 
                OnFinish="HandleSubmit" OnFinishFailed="HandleSubmitFailed">
                    <FormItem Label="连接名称" Required>
                        <Input @bind-Value="@_model.Name" Placeholder="请输入连接名称" />
                    </FormItem>
                    <FormItem Label="数据库类型" Required>
                        <Select TItem="string" TItemValue="string" DefaultValue="@("选择数据库类型")"
                        @bind-Value="@_model.DbType" 
                        Placeholder="请选择数据库类型" 
                        OnSelectedItemChanged="OnDatabaseTypeChanged">
                            <SelectOptions>
                                <SelectOption TItem="string" TItemValue="string" Value="@DatabaseTypeEnum.SQLServer" Label="SQL Server" />
                                <SelectOption TItem="string" TItemValue="string" Value="@DatabaseTypeEnum.MySQL" Label="MySQL" />
                                <SelectOption TItem="string" TItemValue="string" Value="@DatabaseTypeEnum.PostgreSQL" Label="PostgreSQL" />
                                <SelectOption TItem="string" TItemValue="string" Value="@DatabaseTypeEnum.SQLite" Label="SQLite" />
                                <SelectOption TItem="string" TItemValue="string" Value="@DatabaseTypeEnum.Excel" Label="Excel (导入到SQLite)" />
                            </SelectOptions>
                        </Select>
                    </FormItem>
                    @if (_model.DbType != DatabaseTypeEnum.Excel)
                    {
                        <FormItem Label="服务器地址">
                        <Input @bind-Value="@_model.Server" Placeholder="请输入服务器地址" />
                        </FormItem>
                        <FormItem Label="端口号" Required>
                            <AntDesign.InputNumber TValue="int?" @bind-Value="@_model.Port" Placeholder="请输入端口号" Min="1" Max="65535" />
                        </FormItem>
                        <FormItem Label="数据库名称">
                            <Input @bind-Value="@_model.Database" Placeholder="请输入数据库名称" />
                        </FormItem>
                        <FormItem Label="用户名">
                            <Input @bind-Value="@_model.Username" Placeholder="请输入用户名" />
                        </FormItem>
                        <FormItem Label="密码">
                            <InputPassword @bind-Value="@_model.Password" Placeholder="请输入密码" />
                        </FormItem>
                    }
                    else
                    {
                        <FormItem Label="Excel文件" Required>
                            <InputFile OnChange="OnExcelSelected" accept=".xlsx,.xls" />
                        </FormItem>
                        <FormItem Label="导入为库名">
                            <Input @bind-Value="@_excelDbName" Placeholder="用于生成SQLite文件名，例如 sales_db" />
                        </FormItem>
                    }
                    <FormItem Label="连接字符串">
                        @if (_model.DbType == DatabaseTypeEnum.Excel)
                        {
                            <Button Type="@ButtonType.Primary" OnClick="ImportExcelToSqlite" Loading="@_importing" Style="margin-bottom:10px;">导入Excel到SQLite并生成连接</Button>
                        }
                        else
                        {
                            <Button Type="@ButtonType.Primary" OnClick="GenerateConnectionString" Style="margin-bottom:10px;">生成</Button>
                        }
                        <TextArea @bind-Value="@_model.ConnectionString" Placeholder="连接字符串将自动生成，也可手动输入" 
                        AutoSize MinRows="2" MaxRows="6" />
                    </FormItem>
                    <FormItem Label="描述">
                        <TextArea @bind-Value="@_model.Description" Placeholder="请输入描述信息" 
                        AutoSize MinRows="2" MaxRows="6" />
                    </FormItem>
                    <FormItem WrapperColOffset="6" WrapperColSpan="18">
                        <Button Type="primary" HtmlType="submit" Loading="@_submitting">
                            保存
                        </Button>
                        <Button Style="margin-left: 8px;" OnClick="@NavigateToList">
                            返回
                        </Button>
                    </FormItem>
                </Form>
            </Body>
        </Card>
    </ChildContent>
</PageContainer>

@code {
    private Form<DatabaseConnectionConfig> _form;
    private DatabaseConnectionConfig _model = new DatabaseConnectionConfig();
    private bool _submitting = false;
    private bool _importing = false;
    private IBrowserFile _excelFile;
    private string _excelDbName = "excel_db";
    [Parameter]  public string ConId { get; set; }

    protected override void OnInitialized()
    {
        if (!string.IsNullOrEmpty(ConId))
        {
            _model = DatabaseConnectionConfigRepository.GetById(ConId);
        }
        else
        {
            // 设置默认端口
            _model.Port = 0;
        }
    }

    private void OnDatabaseTypeChanged(object value)
    {
        if (value != null && value is string selectedValue && !string.IsNullOrEmpty(selectedValue))
        {
            _model.DbType = selectedValue;

            // 根据数据库类型设置默认端口
            _model.Port = selectedValue switch
            {
                DatabaseTypeEnum.SQLServer => 1433,
                DatabaseTypeEnum.MySQL => 3306,
                DatabaseTypeEnum.PostgreSQL => 5432,
                DatabaseTypeEnum.SQLite => 0,
                DatabaseTypeEnum.Excel => 0,
                _ => 0
            };

            if (selectedValue == DatabaseTypeEnum.Excel)
            {
                _model.Server = string.Empty;
                _model.Username = string.Empty;
                _model.Password = string.Empty;
            }
            else
            {
                // 自动生成连接字符串
                GenerateConnectionString();
            }
        }
    }

    public void GenerateConnectionString()
    {
        _model.ConnectionString = _model.DbType switch
        {
            DatabaseTypeEnum.SQLServer => $"Data Source ={_model.Server},{_model.Port};Initial Catalog ={_model.Database};User ID={_model.Username};Password={_model.Password};Encrypt=True;TrustServerCertificate=True;",
            DatabaseTypeEnum.MySQL => $"Server={_model.Server};Port={_model.Port};Database={_model.Database};Uid={_model.Username};Pwd={_model.Password};",
            DatabaseTypeEnum.PostgreSQL => $"Host={_model.Server};Port={_model.Port};Database={_model.Database};Username={_model.Username};Password={_model.Password};",
            DatabaseTypeEnum.SQLite => $"Data Source={_model.Database};",
            DatabaseTypeEnum.Excel => _model.ConnectionString, // 由导入流程生成
            _ => string.Empty
        };
    }

    private void OnExcelSelected(InputFileChangeEventArgs e)
    {
        _excelFile = e.File;
    }

    private async Task ImportExcelToSqlite()
    {
        if (_excelFile == null)
        {
            _= MessageService.Warning("请先选择Excel文件");
            return;
        }
        _importing = true;
        try
        {
            // 保存到临时目录
            var uploads = Path.Combine(AppContext.BaseDirectory, "uploads");
            Directory.CreateDirectory(uploads);
            var excelPath = Path.Combine(uploads, $"{Guid.NewGuid()}_{_excelFile.Name}");
            await using (var fs = File.Create(excelPath))
            {
                await _excelFile.OpenReadStream(long.MaxValue).CopyToAsync(fs);
            }

            // 解析Excel各Sheet并写入SQLite
            var dbFolder = Path.Combine(AppContext.BaseDirectory, "excel_dbs");
            Directory.CreateDirectory(dbFolder);
            var sqliteFileName = string.IsNullOrWhiteSpace(_excelDbName) ? Path.GetFileNameWithoutExtension(_excelFile.Name) : _excelDbName.Trim();
            var sqlitePath = Path.Combine(dbFolder, $"{sqliteFileName}.db");

            await BuildSqliteFromExcelAsync(excelPath, sqlitePath);

            _model.Database = sqlitePath;
            _model.ConnectionString = $"Data Source={sqlitePath};";
            _= MessageService.Success("Excel 导入 SQLite 成功，已生成连接字符串");
        }
        catch (Exception ex)
        {
            _= MessageService.Error($"导入失败: {ex.Message}");
        }
        finally
        {
            _importing = false;
        }
    }

    private async Task BuildSqliteFromExcelAsync(string excelPath, string sqlitePath)
    {
        // 清空已存在库
        if (File.Exists(sqlitePath))
        {
            try { File.Delete(sqlitePath); } catch { }
        }

        // 使用 Sqlite + SqlSugar 直接建库并插入
        using var db = new SqlSugar.SqlSugarClient(new SqlSugar.ConnectionConfig
        {
            ConnectionString = $"Data Source={sqlitePath};",
            DbType = SqlSugar.DbType.Sqlite,
            IsAutoCloseConnection = true
        });

        // 读取所有sheet
        var sheets = MiniExcel.GetSheetNames(excelPath);
        foreach (var sheet in sheets)
        {
            var rows = MiniExcel.Query(excelPath, sheetName: sheet).ToList();
            if (rows.Count == 0) continue;

            // 列名集合
            var first = (IDictionary<string, object>)rows[0];
            var columns = first.Keys.ToList();

            // 生成建表SQL（简单推断数据类型为TEXT/REAL/INTEGER）
            var safeTable = ToSafeIdentifier(sheet);
            var columnDefs = new List<string>();
            foreach (var col in columns)
            {
                string type = "TEXT";
                foreach (IDictionary<string, object> r in rows)
                {
                    var v = r[col];
                    if (v == null) continue;
                    if (v is int or long) { type = "INTEGER"; break; }
                    if (v is double or float or decimal) { type = "REAL"; break; }
                }
                columnDefs.Add($"[{col}] {type}");
            }
            string createSql = $"CREATE TABLE IF NOT EXISTS [{safeTable}] ({string.Join(",", columnDefs)});";
            await db.Ado.ExecuteCommandAsync(createSql);

            // 批量插入
            foreach (IDictionary<string, object> r in rows)
            {
                var cols = string.Join(",", columns.Select(c => $"[{c}]"));
                var vals = string.Join(",", columns.Select(c => "@" + c));
                string insertSql = $"INSERT INTO [{safeTable}] ({cols}) VALUES ({vals});";
                var parameters = columns.Select(c => new SqlSugar.SugarParameter("@" + c, r[c] ?? DBNull.Value)).ToArray();
                await db.Ado.ExecuteCommandAsync(insertSql, parameters);
            }
        }
    }

    private static string ToSafeIdentifier(string name)
    {
        if (string.IsNullOrWhiteSpace(name)) return "sheet";
        var safe = new string(name.Where(ch => char.IsLetterOrDigit(ch) || ch == '_' || ch == '-').ToArray());
        if (string.IsNullOrWhiteSpace(safe)) safe = "sheet";
        return safe;
    }

    private async Task HandleSubmit()
    {
        _submitting = true;
        try
        {
            GenerateConnectionString();
            if (string.IsNullOrEmpty(_model.Id))
            {
                _model.Id = Guid.NewGuid().ToString();
            }

            // 使用新的更新方法，支持Schema数据清理
            var (success, shouldRetrainSchema, message) = await DatabaseConnectionConfigRepository.UpdateWithSchemaCleanupAsync(_model);
              
            if (success)
            {
                if (shouldRetrainSchema)
                {
                    // 如果需要重新训练Schema，显示特殊提示
                    _= MessageService.Success(message);
                    _= MessageService.Warning("由于数据库连接信息发生变更，建议重新训练Schema以确保数据一致性");
                }
                else
                {
                    _= MessageService.Success(message);
                }
                NavigateToDetail(_model.Id);
            }
            else
            {
                _= MessageService.Error(message);
            }
        }
        catch (Exception ex)
        {
            _= MessageService.Error($"保存失败: {ex.Message}");
        }
        finally
        {
            _submitting = false;
        }
    }

    private void HandleSubmitFailed(EditContext editContext)
    {
        MessageService.Error("表单验证失败，请检查输入");
    }

    private void NavigateToDetail(string id)
    {
        NavigationManager.NavigateTo("/database-connection/details/"+id);
    }

    private void NavigateToList()
    {
        NavigationManager.NavigateTo("/database-connection");
    }
} 